package com.shop.mall.dao.impl;

import com.shop.mall.utils.MysqlDb;
import com.shop.mall.dao.BookDao;
import com.shop.mall.entity.Book;
import com.shop.mall.utils.Pages;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

/**
 * @author admin
 */
public class BookDaoImpl implements BookDao {

    @Override
    public Pages showAll(int currentPage, int pageSize) throws Exception {
        List<Book> list = new ArrayList<Book>();
        Connection connection = MysqlDb.getConnection();
        PreparedStatement ps = null;
        ResultSet rs = null;
        Pages pages = null;
        try {
            ps = connection.prepareStatement("select * from book where deleteFlag = 0");
            System.out.println("执行sql:" + ps);
            rs = ps.executeQuery();
            while (rs.next()) {
                Book book = new Book();
                book.setId(rs.getInt("id"));
                book.setBookname(rs.getString("bookname"));
                book.setIsbn(rs.getString("isbn"));
                book.setPublisher(rs.getString("publisher"));
                book.setPrice(rs.getString("price"));
                book.setUserid(rs.getInt("userid"));
                book.setPublisdate(rs.getString("publisdate"));

                list.add(book);

                pages = new Pages();
                pages.setCurrentPage(1);
                pages.setTotalPage(5);
                pages.setTotalRecord(list.size());
                pages.setList(list);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        MysqlDb.closeAll(rs, ps, connection);
        return pages;
    }

    @Override
    public List<Book> select(int id) throws Exception {
        Connection connection = MysqlDb.getConnection();
        List<Book> list = new ArrayList<Book>();
        PreparedStatement ps = connection.prepareStatement("select * from book where id = ?");
        ps.setObject(1, id);
        System.out.println("执行sql:" + ps);
        ResultSet rs = ps.executeQuery();
        while (rs.next()) {
            Book b = new Book();
            b.setId(rs.getInt("id"));
            b.setBookname(rs.getString("bookname"));
            b.setIsbn(rs.getString("isbn"));
            b.setPublisher(rs.getString("publisher"));
            b.setPrice(rs.getString("price"));
            b.setUserid(rs.getInt("userid"));
            b.setPublisdate(rs.getString("publisdate"));
            list.add(b);
        }
        MysqlDb.closeAll(rs, ps, connection);
        return list;
    }

    @Override
    public int add(Book book) {
        Connection conn = null;
        int line = 0;
        try {
            conn = getConnection();
            String sql = "insert into book(id,bookname,isbn,publisher,price) values (?,?,?,?,?)";
            PreparedStatement ps = conn.prepareStatement(sql);
            ps.setObject(1, book.getId());
            ps.setObject(2, book.getBookname());
            ps.setObject(3, book.getIsbn());
            ps.setObject(4, book.getPublisher());
            ps.setObject(5, book.getPrice());
            System.out.println("执行sql:" + ps);
            line = ps.executeUpdate();
            System.out.println(line > 0 ? "保存成功,更新记录行数:" + line : "保存失败,更新记录行数:" + line);
        } catch (Exception e) {
            System.out.println("发生异常，" + e.getMessage());
            e.printStackTrace();
        }

        return line;
    }

    @Override
    public int update(int id, String bookname, String isbn, String publisher, int price, String publisdate) throws Exception {
        int line = 0;
        Connection connection = MysqlDb.getConnection();
        String sql = "update book set bookname = ?, isbn = ?, publisher = ?, price = ?, publisdate = ? where id = ?";
        PreparedStatement ps = connection.prepareStatement(sql);
        ps.setObject(1, bookname);
        ps.setObject(2, isbn);
        ps.setObject(3, publisher);
        ps.setObject(4, price);
        ps.setObject(5, publisdate);
        ps.setObject(6, id);
        System.out.println("执行sql:" + ps);
        line = ps.executeUpdate();
        System.out.println(line > 0 ? "修改成功,更新记录行数:" + line : "修改失败,更新记录行数:" + line);
        MysqlDb.closeAll(null, ps, connection);
        return line;
    }

    @Override
    public int delete(int id) throws Exception {
//        PreparedStatement cs = getConnection().prepareCall("{call proc_book_delete(?,?)}");
//        cs.registerOutParameter(2, java.sql.Types.INTEGER);
//        cs.execute();
//        r = (int) cs.getObject(2);
//        cs.setObject(1, id);
        //String sql = "delete from book where id = ?";
        String sql = "update book set deleteFlag = 1 where id = ?";
        PreparedStatement ps = getConnection().prepareStatement(sql);
        ps.setObject(1, id);
        System.out.println("执行sql:" + ps);
        int line = ps.executeUpdate();
        System.out.println(line > 0 ? "修改成功,更新记录行数:" + line : "修改失败,更新记录行数:" + line);
        MysqlDb.closeAll(null, ps, getConnection());
        return line;
    }

    private Connection getConnection() throws Exception {
        return MysqlDb.getConnection();
    }
}
